package br.com.mundodaprata.store.data.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;

import br.com.mundodaprata.store.data.NamedRepository;
import br.com.mundodaprata.store.data.RepositoryFactoryJDBC;
import br.com.mundodaprata.store.data.UserRepository;
import br.com.mundodaprata.store.model.Address;
import br.com.mundodaprata.store.model.Client;
import br.com.mundodaprata.store.model.Street;
import br.com.mundodaprata.store.model.User;

public class ClientRepositoryJDBC extends RepositoryJDBC<Client> implements
      NamedRepository<Client> {
   
   
   public ClientRepositoryJDBC() {
      super();
   }

   public ClientRepositoryJDBC(Connection connection) {
      super(connection);
   }

   @Override
   public void insert(Client... elements) throws Exception {
      String sql = "insert into client(name, document, document_type, birthday, email, phone, street, number, credential) values(?, ?, ?, ?, ?, ?, ?, ?, ?);";
      
      PreparedStatement stmt = connection().prepareStatement(sql);
      UserRepository repository = (UserRepository) RepositoryFactoryJDBC.create(User.class);
      
      try {
         for (Client client : elements) {
            repository.insert(client.getUser());
            User selected = repository.selectByUsername(client.getUser().getUsername());
            client.getUser().setCode(selected.getCode());
            
            stmt.setString(1, client.getName());
            stmt.setString(2, client.getDocument());
            stmt.setString(3, client.getDocumentType());
            stmt.setDate(4, new java.sql.Date(client.getBirthday().getTime()));
            stmt.setString(5, client.getEmail());
            stmt.setString(6, client.getPhone());
            stmt.setLong(7, client.getAddress().getDelegate().getCode());
            stmt.setInt(8, client.getAddress().getNumber());
            stmt.setLong(9, client.getUser().getCode());
            
            stmt.addBatch();
         }
         
         stmt.executeBatch();
      }
      finally {
         if (stmt != null && !stmt.isClosed()) {
            stmt.close();
         }
      }
   }
   
   @Override
   public void update(Client... elements) throws Exception {
      String sql = "update client set name = ?, document = ?, document_type = ?, birthday = ?, email = ?, phone = ?, street = ?, number = ? where code = ?;";
      
      PreparedStatement stmt = connection().prepareStatement(sql);
      
      try {
         for (Client client : elements) {
            stmt.setString(1, client.getName());
            stmt.setString(2, client.getDocument());
            stmt.setString(3, client.getDocumentType());
            stmt.setDate(4, new java.sql.Date(client.getBirthday().getTime()));
            stmt.setString(5, client.getEmail());
            stmt.setString(6, client.getPhone());
            stmt.setLong(7, client.getAddress().getDelegate().getCode());
            stmt.setInt(8, client.getAddress().getNumber());
            stmt.setLong(9, client.getCode());
            
            stmt.addBatch();
         }
         
         stmt.executeBatch();
      }
      finally {
         if (stmt != null && !stmt.isClosed()) {
            stmt.close();
         }
      }
   }
   
   @Override
   public void remove(Client... elements) throws Exception {
      String sql = "delete from client where code = ?;";
      
      PreparedStatement stmt = connection().prepareStatement(sql);
      List<User> users = new ArrayList<User>();
      
      try {
         for (Client client : elements) {
            stmt.setLong(1, client.getCode());
            users.add(client.getUser());
            
            stmt.addBatch();
         }
         
         stmt.executeBatch();
         RepositoryFactoryJDBC.create(User.class).remove(users.toArray(new User[users.size()]));
      }
      finally {
         if (stmt != null && !stmt.isClosed()) {
            stmt.close();
         }
      }
   }
   
   @Override
   public Client select(Long key) {
      String sql = "select * from client where code = ?";
      
      try {
         PreparedStatement stmt = connection().prepareStatement(sql);
         stmt.setLong(1, key);

         try {
            ResultSet rs = stmt.executeQuery();
            
            if(rs.next()){
               return toClient(rs);
            }
         }
         finally {
            if (stmt != null && !stmt.isClosed()) {
               stmt.close();
            }
         }
         
      }
      catch (SQLException e) {
         return null;
      }
      
      return null;
   }
   
   @Override
   public List<Client> all() {
      String sql = "select * from client";
      
      try {
         PreparedStatement stmt = connection().prepareStatement(sql);
         
         try {
            ResultSet rs = stmt.executeQuery();
            List<Client> result = new ArrayList<Client>();
            
            while(rs.next()){
               result.add(toClient(rs));
            }
            
            return result;
         }
         finally {
            if (stmt != null && !stmt.isClosed()) {
               stmt.close();
            }
         }
      }
      catch (SQLException e) {
         return Collections.emptyList();
      }
   }
   
   @Override
   public Client select(String name) {
      String sql = "select * from client where name = ?";
      
      try {
         PreparedStatement stmt = connection().prepareStatement(sql);
         stmt.setString(1, name);
         
         try {
            ResultSet rs = stmt.executeQuery();
            
            if(rs.next()){
               return toClient(rs);
            }
         }
         finally {
            if (stmt != null && !stmt.isClosed()) {
               stmt.close();
            }
         }
         
      }
      catch (SQLException e) {
         return null;
      }
      
      return null;
   }
   
   private Client toClient(ResultSet rs) throws SQLException{
      Client client = new Client();
      
      client.setCode(rs.getLong("code"));
      
      Street street = RepositoryFactoryJDBC.create(Street.class).select(rs.getLong("street"));
      Address address = new Address(street);
      address.setNumber(rs.getInt("number"));
      
      client.setAddress(address);
      client.setBirthday(new Date(rs.getDate("birthday").getTime()));
      client.setDocument(rs.getString("document"));
      client.setDocumentType(rs.getString("document_type"));
      client.setEmail(rs.getString("email"));
      client.setName(rs.getString("name"));
      client.setPhone(rs.getString("phone"));
      
      client.setUser(RepositoryFactoryJDBC.create(User.class).select(rs.getLong("credential")));
      
      return client;
   }
   
}